package com.demo2do.boyle.backend.service.customer.impl;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.demo2do.boyle.backend.cache.accessor.PersistentAccessor;
import com.demo2do.boyle.backend.service.customer.ImportExcelService;
import com.demo2do.boyle.common.entity.customer.Customer;
import com.demo2do.boyle.common.entity.customer.CustomerTag;
import com.demo2do.boyle.common.entity.customer.Profile;
import com.demo2do.boyle.common.entity.customer.Sex;
import com.demo2do.boyle.common.entity.customer.Source;
import com.demo2do.boyle.common.entity.customer.social.SocialAccount;
import com.demo2do.boyle.common.entity.customer.wallet.Wallet;
import com.demo2do.boyle.common.entity.geo.Geo;
import com.demo2do.boyle.common.entity.system.tag.Tag;
import com.demo2do.core.BusinessException;
import com.demo2do.core.entity.Result;
import com.demo2do.core.persistence.GenericDaoSupport; 

@Service("importExcelCustomerService")
public class ImportExcelServiceImpl implements ImportExcelService{
	
    private HSSFWorkbook workbook;
     
    @Autowired
    private GenericDaoSupport genericDaoSupport;
    
    @Autowired
	private PersistentAccessor persistentAccessor;

	@SuppressWarnings("deprecation")
	public Result resolveExcel(MultipartFile excelFile,StringBuffer sb){
		
		Result result = new Result();
		try {
			
			InputStream inputStream = excelFile.getInputStream();
			POIFSFileSystem fs = new POIFSFileSystem(inputStream);
			
			workbook = new HSSFWorkbook(fs);
			HSSFSheet sheet = workbook.getSheetAt(0);
			int rowNum = sheet.getLastRowNum();	// 行
			int cellNum;

			HSSFRow row;
			HSSFCell cell;
			for (int i = 1; i <= rowNum; i++) {
				row = sheet.getRow(i);
				cellNum = row.getLastCellNum();
				
				Customer customer = new Customer();
				customer.setMobile("13888888888");
				Profile profile = new Profile();
				
				List<Integer> lifeTagCodes = new ArrayList<Integer>();
				List<Integer> interestTagCodes = new ArrayList<Integer>();
				
				for (int j = 0; j < cellNum; j++) {	// 对一行的每个列进行解析

					cell = row.getCell((short) j);
					
					if (j == 0) {
						if(cell==null || "".equals(cell.toString())){
							sb.append("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，微信昵称不能为空！");
						}else{
							profile.setNickName(cell.toString().trim());
						}
					}else if(j == 1){
						if(cell==null || "".equals(cell.toString())){
							sb.append(sb.length()!=0?("\\n第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，性别不能为空！"):("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，性别不能为空！"));
						}else{
							if ("男".equals(cell.toString())) {
								profile.setSex(Sex.MALE);
							} else {
								profile.setSex(Sex.FEMALE);
							}
						}
						
					}else if(j == 2){
						Geo province = new Geo();
						if(cell==null || "".equals(cell.toString())){
							sb.append(sb.length()!=0?("\\n第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，省份不能为空！"):("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，省份不能为空！"));
						}else{
							if ("北京".equals(cell.toString())) {
								province.setCode("110000");
							}else if("上海".equals(cell.toString())){
								province.setCode("310000");
							}else if("广东".equals(cell.toString())){
								province.setCode("440000");
							}else if("广东".equals(cell.toString())){
								province.setCode("440000");
							}else{
								sb.append(sb.length()!=0?("\\n第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，目前仅支持北京、上海、广州、深圳四城市！"):("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，目前仅支持北京、上海、广州、深圳四城市！"));
							}
						}
						profile.setProvince(province);
					}else if(j == 3){
						Geo city = new Geo();
						if(cell==null || "".equals(cell.toString())){
							sb.append(sb.length()!=0?("\\n第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，城市不能为空！"):("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，城市不能为空！"));
						}else{
							if ("北京".equals(cell.toString())) {
								city.setCode("110100");
							}else if("上海".equals(cell.toString())){
								city.setCode("310100");
							}else if("广州".equals(cell.toString())){
								city.setCode("440100");
							}else if("深圳".equals(cell.toString())){
								city.setCode("440300");
							}else{
								sb.append(sb.length()!=0?("\\n第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，目前仅支持北京、上海、广州、深圳四城市！"):("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，目前仅支持北京、上海、广州、深圳四城市！"));
							}
						}
						profile.setCity(city);
					}else if(j == 4){
						SocialAccount socialAccount = new SocialAccount();
						if(cell==null || "".equals(cell.toString())){
							sb.append(sb.length()!=0?("\\n第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，好友数量不能为空！"):("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，好友数量不能为空！"));
						}else{
							socialAccount.setFans(new BigDecimal(cell.toString()).intValue());
						}
					}else if(j == 5){
						if(cell==null || "".equals(cell.toString())){
							sb.append(sb.length()!=0?("\\n第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，年龄不能为空！"):("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，年龄不能为空！"));
						}else{
							profile.setAge(new BigDecimal(cell.toString()).intValue());
						}
					}else if(j >= 7 && j <= 11){
						if("吃货".equals(cell.toString())){
							interestTagCodes.add(1);
						}else if("旅游".equals(cell.toString())){
							interestTagCodes.add(2);
						}else if("数码".equals(cell.toString())){
							interestTagCodes.add(3);
						}else if("体育".equals(cell.toString())){
							interestTagCodes.add(4);
						}else if("上网".equals(cell.toString())){
							interestTagCodes.add(5);
						}else if("卡通".equals(cell.toString())){
							interestTagCodes.add(6);
						}else if("电影".equals(cell.toString())){
							interestTagCodes.add(7);
						}else if("音乐".equals(cell.toString())){
							interestTagCodes.add(8);
						}else if("美容".equals(cell.toString())){
							interestTagCodes.add(9);
						}else if("购物".equals(cell.toString())){
							interestTagCodes.add(10);
						}else if("游戏".equals(cell.toString())){
							interestTagCodes.add(11);
						}else if("美剧".equals(cell.toString())){
							interestTagCodes.add(12);
						}else{
							sb.append(sb.length()!=0?("\\n第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，兴趣标签请从模板中选取！"):("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，兴趣标签请从模板中选取！"));
						}
					}else if(j >= 13 && j <= 15){
						if("学生".equals(cell.toString())){
							lifeTagCodes.add(1);
						}else if("母婴".equals(cell.toString())){
							lifeTagCodes.add(2);
						}else if("上班".equals(cell.toString())){
							lifeTagCodes.add(3);
						}else if("驾车".equals(cell.toString())){
							lifeTagCodes.add(4);
						}else if("理财".equals(cell.toString())){
							lifeTagCodes.add(5);
						}else if("宠物".equals(cell.toString())){
							lifeTagCodes.add(6);
						}else if("健身".equals(cell.toString())){
							lifeTagCodes.add(7);
						}else if("互联网".equals(cell.toString())){
							lifeTagCodes.add(8);
						}else{
							sb.append(sb.length()!=0?("\\n第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，生活标签请从模板中选取！"):("第"+(i+1)+"行，第"+(j+1)+"列数据格式错误，生活标签请从模板中选取！"));
						}
					}
					
				}
				
				customer.setProfile(profile);
				
				// 1. generate code of customer
				String code = this.generateCustomerCode(Source.SYSTEM);
				
				// 2. generate customer tag
				CustomerTag customerTag = new CustomerTag(customer);
				
				// 3. initialize customer tag
				Tag ageTag = (Tag) persistentAccessor.getAgeValues().get(customer.getProfile().getAge().toString());
				
				Tag areaTag = (Tag) persistentAccessor.getAreaValues().get(customer.getProfile().getCity().getCode());
				
				Tag sexTag = (Tag) persistentAccessor.getSexValues().get(String.valueOf(customer.getProfile().getSex().ordinal()));
				
				customerTag.initialize(ageTag, areaTag, sexTag, lifeTagCodes, interestTagCodes);
				
				// 4. create wallet
				Wallet wallet = new Wallet(customer);
				
				// 5. initialize customer with code, tag and wallet
				customer.initialize(Source.SYSTEM, code, customerTag, wallet);
				
				genericDaoSupport.save(customer);
				
				result.success().message("^_^ 恭喜,导入成功!");
			}
			
		}catch(Exception e){
	    	e.printStackTrace();
	    	throw new BusinessException(e.getMessage());
	    }
	
		return result;
     }
     
	/**
     * 
     * @param source
     * @return
     */
    @SuppressWarnings("unchecked")
    private String generateCustomerCode(Source source) {

        Integer sequence = 1;
        
        List<String> result = genericDaoSupport.searchForList("SELECT code FROM Customer WHERE code LIKE '" + source.getAlphabet() + "%' ORDER BY code DESC", 0, 1);
        
        if(!result.isEmpty()) {
            
            // get code from database search
            String code = result.get(0);
            
            if(code != null)
                sequence = NumberUtils.createBigDecimal(code.substring(1)).intValue() + 1;
        }
        
        // left pad zero chars
        return source.getAlphabet() + StringUtils.leftPad(sequence.toString(), 5, "0");
        
    }
 }